package org.example.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.catalina.User;
import org.apache.ibatis.annotations.*;
import org.example.entity.Users;
import org.example.dto.UserStatisticsDTO;

import java.time.LocalDate;
import java.util.List;

/**
 * @author ljh
 * @version 1.0
 * @Description
 * @date 2025/7/21 15:01
 */
@Mapper
public interface UsersMapper extends BaseMapper<Users> {
    //查询所有用户
    @Select("select id,user_id,openid,unionid,nickname, avatar, balance, member_type, member_expire_time, `status`, created_time, updated_time from users")
    List<Users> getAllUsers();

    //根据openid查询用户
    @Select("select * from users where openid = #{openid}")
    Users getUserByOpenid(String openid);

    //保存新用户
    @Insert("insert into users(nickname, avatar, balance, member_type, member_expire_time, status, created_time, updated_time) " +
            "values(#{nickname}, #{avatar}, #{balance}, #{memberType}, #{memberExpireTime}, #{status}, #{createdTime}, #{updatedTime})")
    int saveUser(Users users);

    //更新用户信息（包括unionid和updated_time）
    @Update("update users set nickname=#{nickname}, avatar=#{avatar},  " +
            "balance=#{balance}, member_type=#{memberType}, member_expire_time=#{memberExpireTime}, " +
            "status=#{status}, updated_time=#{updatedTime} where id=#{id}")
    int updateUserInfo(Users users);

    //根据用户ID查询用户
    @Select("select * from users where id = #{userId}")
    Users getUserById(Long userId);

    //查询当日/月用户总数
    @Select("select \n" +
            "    sum(case when date(updated_time) = current_date() then 1 else 0 end) as today_users,\n" +
            "    sum(case when year(updated_time) = year(current_date()) \n" +
            "             and month(updated_time) = month(current_date()) then 1 else 0 end) as month_users\n" +
            "from users;")
    UserStatisticsDTO countUserStatistics();

    @Select("""
                     SELECT id, user_id, openid, nickname
                         FROM users
                            WHERE DATE(updated_time) = #{date}
                     ORDER BY updated_time DESC
            """)
    List<Users> selectUsersByDate(@Param("date") LocalDate date);

    @Select("SELECT COUNT(*) FROM users \n" +
            "    WHERE DATE(updated_time) = #{date}")
    int countByLastLoginDate(@Param("date") LocalDate date);
}
